﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.SqlClient;
//using System.Data.SqlServerCe;
using System.Data;
using System.Data.OleDb;
//using ErikEJ.Sql;



namespace PAPI_Import
{
    partial class Program
    {
        static void OperateWorkMode5() //Sending a report to TSSC with the changes of last month (1st of month)
        {
            DateTime minusOneMonth = DateTime.Now.AddMonths(-1);
            DateTime firstOfLastMonth = new DateTime(minusOneMonth.Year, minusOneMonth.Month, 1);
            DateTime lastOfLastMonth = firstOfLastMonth.AddMonths(1).AddDays(-1).AddHours(23).AddMinutes(59);
            
            SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);

            string sClearSQL = @"   Select t1.PONumber,t1.Item,t1.Status,t2.First_Name,t2.Last_Name
                                    FROM POes t1
                                    Inner join Papi_Users t2 ON t2.Requisitio=t1.Requisitioner
                                    Where t1.latest=1 AND (t1.Status=1 OR t1.Status=2) 
                                    AND t1.TSSC_Pending_Send_Date BETWEEN @startdate AND @enddate";
                                    
            SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);
            SqlConn.Open();

            SqlCmd.Parameters.AddWithValue("startdate", firstOfLastMonth);
            SqlCmd.Parameters.AddWithValue("enddate", lastOfLastMonth);

            SqlDataReader dr = SqlCmd.ExecuteReader();

            String st = "";
            int counter = 0;
            
            while (dr.Read())
            {
                counter++;
                String sFirstName = dr.GetString(dr.GetOrdinal("First_Name"));
                String sLastName = dr.GetString(dr.GetOrdinal("Last_Name"));
                Int64 sPONUmber = dr.GetInt64(dr.GetOrdinal("PONumber"));
                int sItem = dr.GetInt32(dr.GetOrdinal("Item"));
                int iStatus = dr.GetInt32(dr.GetOrdinal("status"));

                st += "PO Number: " + sPONUmber + ", Item: " + sItem + ", Owner: " + sFirstName + " " + sLastName + ", Status: " + (iStatus == 1 ? "Pending Close" : "Pending Delivery Date") + Environment.NewLine;
            }

            dr.Close();
            SqlConn.Close();

            if (counter != 0)
            {
                try
                {
                    PapiImportSendMail(MailType.WarningMail, getSpecialUsersEmails(UserType.TSSC),
                           "Hello TSSC, " + Environment.NewLine +  Environment.NewLine + 
                           "Last Month, from " + firstOfLastMonth.ToShortDateString() + " until " + lastOfLastMonth.ToShortDateString() +
                           ", users updated POs and items and your attention is needed: " + Environment.NewLine + Environment.NewLine
                          + st + Environment.NewLine + "Thank you," + Environment.NewLine + "POMP System",true);

                    writeToLog("Sent " + counter + " reminder(s) to TSSC");
                }
                catch (NoUsersException e)
                {
                    writeToLog("Error: No TSSC users found in the system. Unable to send reminders.");
                }
            }
            else
            {
                writeToLog("No Reminders sent. None was needed.");
            }

            return;
        }
    }
}
